﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Skyworth.CRM.Service.Entity;
using Skyworth.CRM.Service.IDAL;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Skyworth.CRM.DAL
{
    public class ModulesDAL : SuperDataBase, IModulesDAL
    {
        public void Add(Modules model)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into T_SYS_Modules(");
                strSql.Append("ModuleID,ParentModuleID,ModuleName,ModuleURL,ModuleDescription,IsDisplay,Sno)");
                strSql.Append(" values (");
                strSql.Append("@ModuleID,@ParentModuleID,@ModuleName,@ModuleURL,@ModuleDescription,@IsDisplay,@Sno)");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@ModuleID", DbType.Int32, model.ModuleId);
                DB.AddInParameter(cmd, "@ParentModuleID", DbType.Int32, model.ParentModuleId);
                DB.AddInParameter(cmd, "@ModuleName", DbType.String, model.ModuleName);
                DB.AddInParameter(cmd, "@ModuleURL", DbType.String, model.ModuleURL);
                DB.AddInParameter(cmd, "@ModuleDescription", DbType.String, model.ModuleDescription);
                DB.AddInParameter(cmd, "@IsDisplay", DbType.Boolean, model.IsDisplay);
                DB.AddInParameter(cmd, "@Sno", DbType.Int32, model.Sno);
                DB.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Update(Modules model)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update T_SYS_Modules set ");
                strSql.Append("ParentModuleID=@ParentModuleID,");
                strSql.Append("ModuleName=@ModuleName,");
                strSql.Append("ModuleURL=@ModuleURL,");
                strSql.Append("ModuleDescription=@ModuleDescription,");
                strSql.Append("IsDisplay=@IsDisplay,");
                strSql.Append("Sno=@Sno");
                strSql.Append(" where ModuleID=@ModuleID ");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@ModuleID", DbType.Int32, model.ModuleId);
                DB.AddInParameter(cmd, "@ParentModuleID", DbType.Int32, model.ParentModuleId);
                DB.AddInParameter(cmd, "@ModuleName", DbType.String, model.ModuleName);
                DB.AddInParameter(cmd, "@ModuleURL", DbType.String, model.ModuleURL);
                DB.AddInParameter(cmd, "@ModuleDescription", DbType.String, model.ModuleDescription);
                DB.AddInParameter(cmd, "@IsDisplay", DbType.Boolean, model.IsDisplay);
                DB.AddInParameter(cmd, "@Sno", DbType.Int32, model.Sno);
                DB.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetModuleFunc(int UserId)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select ModuleID,FunctionID from T_SYS_RoleAuthority as tsra");
                strSql.Append(" where Flag = 1 and exists(select * from T_SYS_UserRoles as tsu where tsra.RoleID = tsu.RoleID");
                strSql.Append(" and tsu.UserID = @userid) union select ModuleID,FunctionID from T_SYS_UserAuthority as tsua");
                strSql.Append(" where Flag = 1 and tsua.UserID = @userid");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@userid", DbType.Int32, UserId);                
                return DB.ExecuteDataSet(cmd).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetModules(int UserId,int FunctionId)
        { 
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * from T_SYS_Modules as tsm where exists(select * from");
                strSql.Append(" (select ModuleID,FunctionID from T_SYS_RoleAuthority as tsra");
                strSql.Append(" where Flag = 1 and exists(select * from T_SYS_UserRoles as tsu where tsra.RoleID = tsu.RoleID");
                strSql.Append(" and tsu.UserID = @userid) union");
                strSql.Append(" select ModuleID,FunctionID from T_SYS_UserAuthority as tsua ");
                strSql.Append(" where Flag = 1 and tsua.UserID = @userid) as tmm where tsm.ModuleID=tmm.ModuleID ");
                strSql.Append(" and (@functionid = 0 or tmm.FunctionID = @functionid))");
                strSql.Append(" order by Sno");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@userid", DbType.Int32, UserId);
                DB.AddInParameter(cmd, "@FunctionId", DbType.Int32, FunctionId);                
                return DB.ExecuteDataSet(cmd).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable Query(string strWhere)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select ModuleID,ParentModuleID,ModuleName,ModuleURL,ModuleDescription,IsDisplay,Sno ");
                strSql.Append(" FROM T_SYS_Modules ");
                DataSet ds = DB.ExecuteDataSet(CommandType.Text, strSql.ToString() + strWhere);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }



        //取所有的非页面的模块
        public DataTable GetModules()
        {
            return Query(" where ModuleURL is null order by Sno asc");
        }

        public DataTable GetPagesAuthorityByUser(int parentPageId, int userId)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select tsm.ModuleID,tsm.ModuleName,moduleURL,dbo.[fn_GetFunctionId](@UserID,tsm.ModuleID) as pageAuthority");
                strSql.Append(" from T_SYS_Modules as tsm where tsm.ParentModuleID = @ParentModuleID");
                strSql.Append(" order by tsm.Sno");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@UserID", DbType.Int32, userId);
                DB.AddInParameter(cmd, "@ParentModuleID", DbType.Int32, parentPageId);
                DataSet ds = DB.ExecuteDataSet(cmd);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //public DataTable GetPagesAuthorityByUser(int parentPageId)
        //{
        //    try
        //    {
        //        StringBuilder strSql = new StringBuilder();
        //        strSql.Append("select * from T_SYS_Modules where ParentModuleID = @ParentModuleID");
        //        strSql.Append(" order by Sno");
        //        DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
        //        DB.AddInParameter(cmd, "@ParentModuleID", DbType.Int32, parentPageId);
        //        DataSet ds = DB.ExecuteDataSet(cmd);
        //        return ds.Tables[0];
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //}

        //取某个角色拥有的某个页面（包括这个页面拥有那些功能，这个角色的这个页面所拥有的功能）
        public DataTable GetPagesWithFunctionAndAuthorityByRole(int moduleId, int roleId)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT  dbo.fn_GetPageFunctionList(MenuID) AS pageFunction,");
                strSql.Append(" dbo.fn_GetPageAuthorityList(MenuID,@roleID) AS pageAuthority,");
                strSql.Append(" MenuID, MenuName, MenuURL, MenuDesc, IsDisplay, SNO, Level ");
                strSql.Append(" FROM dbo.fn_GetMenuTree(@parentID) AS menuTreeTable");
                strSql.Append(" WHERE MenuURL IS not NULL  order by SNO asc");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@parentID", DbType.Int32, moduleId);
                DB.AddInParameter(cmd, "@roleID", DbType.Int32, roleId);
                DataSet ds = DB.ExecuteDataSet(cmd);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        //取所有的页面包含（某个用户最终拥有的功能）（包括这个页面拥有那些功能，这个用户最终拥有的这个页面的功能）
        public DataTable GetPagesWithFinalFunctionAndAuthorityByUser(int moduleId, int userId)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT  dbo.fn_GetPageFunctionList(MenuID) AS pageFunction,");
                strSql.Append(" dbo.fn_GetPageFinalAuthorityByUser(MenuID,@userId) AS pageAuthority,");
                strSql.Append(" MenuID, MenuName, MenuURL, MenuDesc, IsDisplay, SNO, Level ");
                strSql.Append(" FROM dbo.fn_GetMenuTree(@parentID) AS menuTreeTable");
                strSql.Append(" WHERE MenuURL IS not NULL  order by SNO asc");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@parentID", DbType.Int32, moduleId);
                DB.AddInParameter(cmd, "@userId", DbType.Int32, userId);
                DataSet ds = DB.ExecuteDataSet(cmd);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
